package com.emo.wowoblog.dao;

import com.emo.wowoblog.entity.Article;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface ArticleDao extends JpaRepository<Article, Integer> {

//查询所有博客，原生SQL查询，返回博客视图

    @Query(value="select article.aid,article.create_time, article.browse_number, article.catalog, catalog.cname,article.content, \n" +
            "article.reply_number, article.tags, article.title,article.type, questiontype.name, article.uid, users.username \n" +
            "from article left join users on article.uid = users.uid \n" +
            "left join catalog on article.catalog = catalog.cid \n" +
            "left join questiontype on article.type = questiontype.id order by article.create_time desc",nativeQuery = true)
    Object[] queryAllArticleViews();

    /**
     * 查询该板块下所有的博文
     * @param catalogName
     * @return
     */
    @Query(value="select article.aid,article.create_time, article.browse_number, article.catalog, catalog.cname,article.content, \n" +
            "article.reply_number, article.tags, article.title,article.type, questiontype.name, article.uid, users.username \n" +
            "from article left join users on article.uid = users.uid \n" +
            "left join catalog on article.catalog = catalog.cid \n" +
            "left join questiontype on article.type = questiontype.id where catalog.cname=?1 order by article.create_time desc",nativeQuery = true)
    Object[] queryAllArticleViewsByCatalogName(String catalogName);
    /**
     * 根据问题类型，该问题下的所有博文
     * @param questionTypeName
     * @return
     */
    @Query(value="select article.aid,article.create_time, article.browse_number, article.catalog, catalog.cname,article.content, \n" +
            "article.reply_number, article.tags, article.title,article.type, questiontype.name, article.uid, users.username \n" +
            "from article left join users on article.uid = users.uid \n" +
            "left join catalog on article.catalog = catalog.cid \n" +
            "left join questiontype on article.type = questiontype.id where questiontype.name=?1 order by article.create_time desc",nativeQuery = true)
    Object[] queryAllArticleViewsByQuestionTypename(String questionTypeName);

    //根据编号查询指定的博客，查询某个博客的具体信息
    @Query(value="select article.aid,article.create_time, article.browse_number, article.catalog, catalog.cname,article.content, \n" +
            "article.reply_number, article.tags, article.title,article.type, questiontype.name, article.uid, users.username \n" +
            "from article left join users on article.uid = users.uid \n" +
            "left join catalog on article.catalog = catalog.cid \n" +
            "left join questiontype on article.type = questiontype.id where article.aid = ?1",
            nativeQuery = true)
    Object[] queryArticleViewByAid(Integer aid);




}
